package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Table;
import utils.JdbcUtil;

public class TableDao {
	public List<Table> getTableList(String dbId){
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
		List<Table> tableList = null;
	
		try {
			con = JdbcUtil.getCon();
			String sql = "select * from t_tables where dbId = " + dbId;
			tableList = new ArrayList<Table>();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Table table = new Table();
				table.setId(rs.getInt("id"));
				table.setTableName(rs.getString("tableName"));
				table.setDbId(rs.getInt("dbId"));
				tableList.add(table);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return tableList;
	}

	public List<Table> getAllTableList(){
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
		String sql = "select * from t_tables order by dbId";
		List<Table> tableList = new ArrayList<Table>();
		
		try {
			con = JdbcUtil.getCon();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Table table = new Table();
				table.setId(rs.getInt("id"));
				table.setTableName(rs.getString("tableName"));
				table.setDbId(rs.getInt("dbId"));
				tableList.add(table);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return tableList;
	}

	public int addTable(Table tab) {
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
		
		String sql = "insert into t_tables (tableName,dbid) values(?,?)";
		int result = -1;
		try {
			con = JdbcUtil.getCon();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, tab.getTableName());
			pstmt.setInt(2, tab.getDbId());
			result =  pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return result;
	}
	public int deleteTable(String id){
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
		String sql = "delete from dbo.t_tables where id ="+id;
		int result = -1;
		try {
			con = JdbcUtil.getCon();
			pstmt = con.prepareStatement(sql);
			result =  pstmt.executeUpdate();
		} catch (SQLException e) {
			
			return 0;
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return result;
	}
	public String getTablename(String id){
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
		String sql ="select * from dbo.t_tables where id="+id;
		
		String result = null;
		try {
			con = JdbcUtil.getCon();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			result = null;
			if(rs.next()){
				result = rs.getString("tableName");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}	
	    return result;
		
	}
	public List<Table> getTableListbyname(String name){
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
		String sql = "select * from t_tables where tableName like '%" + name+"%'";
		List<Table> tableList = new ArrayList<Table>();
		
		try {
			con = JdbcUtil.getCon();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Table table = new Table();
				table.setId(rs.getInt("id"));
				table.setTableName(rs.getString("tableName"));
				table.setDbId(rs.getInt("dbId"));
				tableList.add(table);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}	
		return tableList;
	}
	public String getdbId(String id){
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
		String sql ="select * from dbo.t_tables where id="+id;
		
		String result = null;
		try {
			con = JdbcUtil.getCon();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			result = null;
			if(rs.next()){
				result = rs.getString("dbId");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}	
	    return result;
		
	}
}
